IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pOutProdDetail_Delete'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pOutProdDetail_Delete
GO

CREATE PROC dbo.pOutProdDetail_Delete
  @id        INT
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/

                                        -- error has occurred and no other
                                        -- specific error condition is
                                        -- assigned.

/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT,
    @amount			INT,
    @inProdDetailId	INT,
    @productId		INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/
	
	BEGIN TRANSACTION
	BEGIN TRY
	
	SELECT @amount = Amount, 
		@productId = ProductId
	FROM OutProdDetail
	WHERE Id = @id
	
	-- Delete out prod detail	
	DELETE FROM OutProdDetail
	WHERE Id = @id	

	UPDATE Product
	SET Amount = (Amount + @amount)
	WHERE Id = @productId
	
	-- Return value 1 indicates success
	SET @intReturn = 1
	COMMIT TRANSACTION

	END TRY
	BEGIN CATCH
		SET @intReturn = -1
		ROLLBACK TRANSACTION
	END CATCH  

lblExit:

  RETURN @intReturn

/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 1)) BEGIN
    SET @intReturn = @intErrNo

  END

 GOTO lblExit


